package com.motu.vertx.module.utility.webhandler;

import com.motu.vertx.module.utility.webtool.BaseWebHandler;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.motu.vertx.module.utility.postgresql.PostgreUtils;
import com.motu.vertx.module.utility.toolset.Tool;

import io.vertx.core.http.HttpServerResponse;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.sql.SQLClient;
import io.vertx.ext.web.RoutingContext;
import io.vertx.ext.web.client.WebClient;
public class Recordlog2Handler extends BaseWebHandler{
	private static final Logger logger = LoggerFactory.getLogger(Recordlog2Handler.class);

	private static SQLClient jdbcClientLog = null;

	public static void init(SQLClient jdbcLog){
		jdbcClientLog = jdbcLog;
	}

	/**
	 * 生成with 部分语句的字符串
	 * @param json
	 * @return
	 */
	private static String getWithStr(JsonObject json) {
		String str = "WITH t_item as (select * from " + json.getString("tbname") + " where atime >= '" + json.getString("stime") + "' and atime <= '" + json.getString("etime") + "' ";
		if (json.containsKey("userid")) {
			str += " and userid = " + json.getLong("userid");
		}
		if (json.containsKey("serverid")) {
			str += " and serverid = " + json.getInteger("serverid");
		}
		if (json.containsKey("logtype")) {  //日志类型
			str += " and type = " + json.getInteger("logtype") ;
		}
		if (json.containsKey("itemid")) {  //具体某个itemid
			str += " and itemid = " + json.getInteger("itemid");
		}
		str += ") ";
		return str;
	}

	/*
	 * 获取查询条件语句
	 */
	private static String getWhereStr(JsonObject json){
		String str = " where 1=1 ";
		if (json.containsKey("serverids")) {
			str += " and serverid =ANY (ARRAY [" + json.getString("serverids") + " ])";
		}
		if (json.containsKey("minitemid")) {   //itemid范围
			str += " and itemid >=  " + json.getInteger("minitemid");
		}
		if (json.containsKey("maxitemid")) {
			str += " and itemid <=  " + json.getInteger("maxitemid");
		}
		if (json.containsKey("param")) {
			str += " and param like '%" + json.getString("param") + "%' ";
		}
		if (json.containsKey("type")) {  //有选消耗还是获得
			String tmp = "";
			if (json.getInteger("type") == 1) {  //消耗，查consume 字段
				str += " and consume > 0 ";
				tmp = " and consume ";
			} else {
				str += " and income > 0 ";
				tmp = "and income ";
			}
			if (json.containsKey("value")) {
				str += tmp + " =  " + json.getInteger("value");
			} else {
				if (json.containsKey("minvalue")) {
					str += tmp + " >=  " + json.getInteger("minvalue");
				}
				if (json.containsKey("maxvalue")) {
					str += tmp + " <=  " + json.getInteger("maxvalue");
				}
			}
		}
		return str;
	}
	
	
	/**
	 * 消耗获得查询,只查询分页
	 */
	public static void search_limit(RoutingContext routingContext,JsonObject json,String adminName,WebClient client){
		HttpServerResponse response = routingContext.response();
		JsonArray params = new JsonArray();

		int pageNo = json.getInteger("pageNo");
		int pageSize = json.getInteger("pageSize");
		int start = pageNo*pageSize;
		String sql = getWithStr(json) + "select * from t_item " + getWhereStr(json);
		sql += " order by atime desc limit " + pageSize + " offset " + start + ";";
		long startTime = System.currentTimeMillis();
		logger.info(Tool.getSqlString(sql, params));
		String sql2 = sql;
		System.out.println(sql);
		System.out.println(params);
		PostgreUtils.queryJsonArrayFromDb(jdbcClientLog, sql, params, handler->{
			if (handler.succeeded()) {
				int queryTime = (int)(System.currentTimeMillis()-startTime)/1000;
				JsonObject result = new JsonObject();
				result.put("rows", handler.result());
				sendHttpResponse(response, result);
				if(queryTime >= 10) {//大于10秒的查询，打印一下
					recordQueryLongTime(jdbcClientLog, sql2, params, "", new JsonArray(), queryTime);
				}
			} else {
				sendHttpResponseError(response,  handler.cause().getMessage());
			}
		});
	}
	
	/**
	 * 消耗获得查询,只查询总条数
	 */
	public static void search_count(RoutingContext routingContext,JsonObject json,String adminName,WebClient client){
		HttpServerResponse response = routingContext.response();
		JsonArray params = new JsonArray();

		String sql = getWithStr(json) + "select count(*) as total from t_item " + getWhereStr(json);
		long startTime = System.currentTimeMillis();
		logger.info(Tool.getSqlString(sql, params));
		PostgreUtils.queryJsonArrayFromDb(jdbcClientLog, sql, params, handler->{
			if (handler.succeeded()) {
				int queryTime = (int)(System.currentTimeMillis()-startTime)/1000;
				JsonObject result = new JsonObject();
				int total = handler.result().getJsonObject(0).getInteger("total");
				result.put("total", total);
				sendHttpResponse(response, result);
				if(queryTime >= 10) {//大于10秒的查询，打印一下
					recordQueryLongTime(jdbcClientLog, sql, params, "", new JsonArray(), queryTime);
				}
			} else {
				sendHttpResponseError(response,  handler.cause().getMessage());
			}
		});
	}
	
	/**
	 * 统计消耗获得的总和
	 */
	public static void fetchtotal(RoutingContext routingContext,JsonObject json,String adminName,WebClient client){
		HttpServerResponse response = routingContext.response();
		JsonArray params = new JsonArray();
		String sql = getWithStr(json) + "select sum(consume) as consume, sum(income) as income from t_item " + getWhereStr(json);
		long startTime = System.currentTimeMillis();
		logger.info(Tool.getSqlString(sql, params));
		PostgreUtils.queryJsonArrayFromDb(jdbcClientLog, sql, params, handler->{
			if (handler.succeeded()) {
				int queryTime = (int)(System.currentTimeMillis()-startTime)/1000;
				JsonArray list = handler.result();
				JsonObject result = new JsonObject();
				if(list.size() > 0) {
					result.put("consumetotal", list.getJsonObject(0).getString("consume"));
					result.put("incometotal", list.getJsonObject(0).getString("income"));
				}else {
					result.put("consumetotal", 0);
					result.put("incometotal", 0);
				}
				sendHttpResponse(response, result);
				if(queryTime >= 10) {//大于10秒的查询，打印一下
					recordQueryLongTime(jdbcClientLog, sql, params, "", new JsonArray(), queryTime);
				}
			} else {
				sendHttpResponseError(response, handler.cause().getMessage());
			}
		});
	}
	
	/**
	 * 用户汇总查询
	 */
	public static void statuser(RoutingContext routingContext,JsonObject json,String adminName,WebClient client){
		String str1 = " ", str2 = " ";
		if (json.getInteger("type") == 1) {  //消耗
			str1 = "sum(consume) as count";
			str2 = " and consume > 0";
		}
		if (json.getInteger("type") == 2) {  //获得
			str1 = "sum(income) as count";
			str2 = " and income > 0";
		}
		JsonArray params = new JsonArray();
		String tbname = json.getString("tbname");
		if (json.containsKey("tbname")) {
			tbname = json.getString("tbname");
		}
		String sql = "select userid, type, " + str1 + " from " + tbname + " where atime >= '" + json.getString("stime") + "' and atime <= '" + json.getString("etime") + "' " + str2 ;
//		String sql = "select userid, type, " + str1 + " from " + tbname + " where atime >=? and atime <= ? " + str2 ;
//		params.add(json.getString("stime")).add(json.getString("etime"));
		if (json.containsKey("userid")) {
			sql += " and userid =  " + json.getLong("userid");
//			sql += " and userid = ? ";
//			params.add(json.getLong("userid"));
		}
		if (json.containsKey("logtype")) {  //日志类型
			sql += " and type =  " + json.getInteger("logtype");
//			sql += " and type = ? ";
//			params.add(json.getInteger("logtype"));
		}
		if (json.containsKey("itemid")) {  //具体某个itemid
			sql += " and itemid =  " + json.getInteger("itemid");
//			sql += " and itemid = ? ";
//			params.add(json.getInteger("itemid"));
		} 
		if (json.containsKey("itemids")) {  //查询itemid范围
			sql += " and itemid =ANY (ARRAY [" + json.getString("itemids") + " ])";
		} 
		sql += " group by userid,type order by count desc;";
		logger.info(Tool.getSqlString(sql, params));
		PostgreUtils.queryJsonArrayFromDb(jdbcClientLog, sql, params, handler->{
			if (handler.succeeded()){
				JsonArray list = handler.result();
				sendHttpResponse(routingContext.response(), new JsonObject().put("rows",list));
			} else{
				sendHttpResponseError(routingContext.response(), handler.cause().getMessage());
			}
		});
	}
	
	/**
	 * 全服汇总查询
	 */
	public static void statserver(RoutingContext routingContext,JsonObject json,String adminName,WebClient client){
		String str1 = "", str2 = "";
		if (json.getInteger("type") == 1) {  //消耗
			str1 = "sum(consume) as count";
			str2 = " and consume > 0";
		}
		if (json.getInteger("type") == 2) {  //获得
			str1 = "sum(income) as count";
			str2 = " and income > 0";
		}
		JsonArray params = new JsonArray();
		String tbname = json.getString("tbname");
		if (json.containsKey("tbname")) {
			tbname = json.getString("tbname");
		}
		String sql = "select type, to_char(atime,'YYYY-MM-DD') as date," +str1+ " from " + tbname + " where atime >= '" + json.getString("stime") + "' and atime <= '" +json.getString("etime") +"' "  +str2;
//		String sql = "select type, to_char(atime,'YYYY-MM-DD') as date," +str1+ " from " + tbname + " where atime >= ? and atime <= ? "+str2;
//		params.add(json.getString("stime")).add(json.getString("etime"));
		if (json.containsKey("serverid")) {
			sql += " and serverid =  " + json.getInteger("serverid") ;
//			sql += " and serverid = ? ";
//			params.add(json.getInteger("serverid"));
		}
		if (json.containsKey("logtype")) {  //日志类型
			sql += " and type = " + json.getInteger("logtype");
//			sql += " and type = ? ";
//			params.add(json.getInteger("logtype"));
		}
		if (json.containsKey("itemid")) {  //具体某个itemid
			sql += " and itemid = " + json.getInteger("itemid");
			
//			sql += " and itemid = ? ";
//			params.add(json.getInteger("itemid"));
		} 
		if (json.containsKey("itemids")) {  //查询itemid范围
			sql += " and itemid =ANY (ARRAY [" + json.getString("itemids") + " ])";
		} 
		sql += " group by date, type;";
		logger.info(Tool.getSqlString(sql, params));
		PostgreUtils.queryJsonArrayFromDb(jdbcClientLog, sql, params, handler->{
			if (handler.succeeded()){
				JsonArray list = handler.result();
				sendHttpResponse(routingContext.response(), new JsonObject().put("rows",list));
			} else{
				sendHttpResponseError(routingContext.response(), handler.cause().getMessage());
			}
		});
	}
}
